# 增删改查
from flask import Flask
from flask_sqlalchemy import SQLAlchemy
from sqlalchemy import and_



app = Flask(__name__)

# 配置数据库连接
class Config:
    '''配置数据库连接参数'''
    SQLALCHEMY_DATABASE_URI = "mysql://root:root@localhost:3306/flask_db"


# 将数据库配置匹配到Flask
app.config.from_object(Config)

# 创建数据库连接对象
db = SQLAlchemy(app)

# 部门表类
class Dept(db.Model):
    '''部门表'''
    __tablename__ = "dept"
    id = db.Column(db.Integer,primary_key = True) # 设置主键
    name = db.Column(db.String(32),unique=True) # 设置部门名称唯一

    def __repr__(self):
        return self.name

# 员工表类
class Employee(db.Model):
    '''员工表'''
    __tablename__ = "emp"
    id = db.Column(db.Integer,primary_key=True)
    name = db.Column(db.String(50),unique=True)
    password = db.Column(db.String(255))
    # 设置外键
    dept_id = db.Column(db.Integer,db.ForeignKey("dept.id"))

    def __repr__(self):
        return "{" + f"name={self.name},password={self.password},dept_id={self.dept_id}" +"}"

# 增
def dept_add():
    dept1 = Dept(name="教学部")
    dept2 = Dept(name="研发部")
    dept3 = Dept(name="后勤部")

    db.session.add(dept1)
    db.session.add_all([dept2,dept3])

    # 手动提交
    db.session.commit()

def emp_add():
    emps = [
        Employee(name="聂茹凤",password="123456",dept_id=1),
        Employee(name="李伟聪", password="123456", dept_id=2),
        Employee(name="谭鑫宇", password="123456", dept_id=1),
        Employee(name="王银庆", password="123456", dept_id=3),
    ]

    db.session.add_all(emps)

    db.session.commit()

# 查
# 全查（查询所有）
def find_all_emp():
    emps = Employee.query.all()
    print(emps)

# 根据id 查询
def find_emp_id(id):
    # emp = Employee.query.get(1) # Flask1.0 提供的，可用，但有警告，属于过时方法
    emp = db.session.get(Employee,1) # Flask2.0提供的
    print(emp)

# 根据name查询
def find_emp_name(name):
    # emp = Employee.query.filter_by(name=name).first()
    emp = Employee.query.filter(Employee.name == name).all()[0]
    print(emp)

# 根据name进行模糊查询
def find_emp_like_name(name):
    emp = Employee.query.filter(Employee.name.like(f"{name}%")).all()
    print(emp)

# 多条件查询
def find_emp_and(name,dept):
    emp = Employee.query.filter(and_(Employee.name.like(f"%{name}%"),Employee.dept_id==dept)).all()
    print(emp)
# 改
def update_emp(id,name):
    # Employee.query.get(id).name = name
    db.session.get(Employee,id).name = name
    db.session.commit()

# 删
def del_emp_id(id):
    db.session.delete(db.session.get(Employee,id))
    db.session.commit()



if __name__ == '__main__':
    with app.app_context(): # 上下文
       # dept_add()
       # emp_add()
       #  find_all_emp()
      # find_emp_name("王银庆")
      #  find_emp_like_name("谭")
      # find_emp_and("谭",2)
      # update_emp(5,"武松")
      del_emp_id(5)
